#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "insert into hive.edu_dm.kxh_dm_web_chat_ems_rollup
with tmp as (select
       substring(create_time_day,1,4) as year_code,
case when substring(create_time_day,6,2) = '01' then '第一季度'
    when substring(create_time_day,6,2) = '02' then '第一季度'
    when substring(create_time_day,6,2) = '03' then '第一季度'
    when substring(create_time_day,6,2) = '04' then '第二季度'
    when substring(create_time_day,6,2) = '05' then '第二季度'
    when substring(create_time_day,6,2) = '06' then '第二季度'
    when substring(create_time_day,6,2) = '07' then '第三季度'
    when substring(create_time_day,6,2) = '08' then '第三季度'
    when substring(create_time_day,6,2) = '09' then '第三季度'
    when substring(create_time_day,6,2) = '10' then '第四季度'
    when substring(create_time_day,6,2) = '11' then '第四季度'
    when substring(create_time_day,6,2) = '12' then '第四季度'
    else null end as quarter_code,
       substring(create_time_day,1,7) as year_month,
       create_time_day,
       area,
       seo_source,
       origin_channel,
       from_url,
       group_type,
       session_id_count,
       sid_count,
       ip_count,
       con_rate
from hive.edu_dws.kxh_dws_web_chat_ems_day_cnt)
select year_code,
       quarter_code,
       year_month,
       create_time_day,
        --时间分组标记
        case when grouping (create_time_day)=0 then  'day'
             when  grouping (year_month)=0 then 'year_month'
             when  grouping (quarter_code)=0 then 'quarter_code'
             when  grouping (year_code)=0 then 'year_code'
             else 'other' end as time_type,
       area,
       seo_source,
       origin_channel,
       from_url,
       --按年的5个围堵组合
       case when grouping (area)=0 then  'area'
             when  grouping (origin_channel)=0 then 'origin_channel'
             when  grouping (seo_source)=0 then 'seo_source'
             when  grouping (from_url)=0 then 'from_url'
             else 'all' end as group_type,
       --指标
       sum(session_id_count) as session_id_count,
       sum(sid_count) as sid_count,
       sum(ip_count) as ip_count,
       case when sum(sid_count)>0 AND
        grouping(area)=0 then sum(session_id_count)*1.00 / sum(sid_count)
        end as con_rate
from tmp
group by
grouping sets (
     --天
       (year_code,quarter_code,year_month,create_time_day,group_type),
       (year_code,quarter_code,year_month,create_time_day,area,group_type),
       (year_code,quarter_code,year_month,create_time_day,origin_channel,group_type),
       (year_code,quarter_code,year_month,create_time_day,seo_source,group_type),
       (year_code,quarter_code,year_month,create_time_day,from_url,group_type),
  -- 月
       (year_code,quarter_code,year_month,group_type),
       (year_code,quarter_code,year_month,area,group_type),
       (year_code,quarter_code,year_month,origin_channel,group_type),
       (year_code,quarter_code,year_month,seo_source,group_type),
       (year_code,quarter_code,year_month,from_url,group_type),
  --季度
       (year_code,quarter_code,group_type),
       (year_code,quarter_code,area,group_type),
       (year_code,quarter_code,origin_channel,group_type),
       (year_code,quarter_code,seo_source,group_type),
       (year_code,quarter_code,from_url,group_type),
  --年
       (year_code,group_type),
       (year_code,area,group_type),
       (year_code,origin_channel,group_type),
       (year_code,seo_source,group_type),
       (year_code,from_url,group_type)
    );
"
